Excel BI - Excel Challenge 685

🔰 List the tasks which are overlapping.

Illustration for Excel BI - Excel Challenge 685

Challenge Description

🔰 List the tasks which are overlapping. For ex. B’s end date is 24-Feb whereas C’s start date is 22-Feb. Hence, B overlaps with C.

Solutions

R Solution

library(tidyverse)
library(readxl)
library(lubridate)
library(igraph)

path = "Excel/685 Overlapping Tasks.xlsx"
input = read_excel(path, range = "A1:C8")
test  = read_excel(path, range = "E1:E4")

input$interval = interval(input$`Planned Start Date`, input$`Planned End Date`)

tasks = expand.grid(input$Task, input$Task, stringsAsFactors = FALSE) %>%
  left_join(input, by = c("Var1" = "Task")) %>%
  left_join(input, by = c("Var2" = "Task")) %>%
  select(Task1 = Var1, Task2 = Var2, interval1 = interval.x, interval2 = interval.y) %>%
  filter(Task1 < Task2) %>%
  mutate(overlap = int_overlaps(interval1, interval2)) %>%
  filter(overlap) %>%
  select(Task1, Task2)

g = graph_from_data_frame(tasks, directed = FALSE)
subgraphs = decompose.graph(g) %>%
  map(~V(.)$name) %>%
  map_chr(~paste(., collapse = ", ")) %>%
  data.frame(ans = .) %>%
  arrange(ans)

all.equal(subgraphs$ans, test$`Anwer Expected`, check.attributes = FALSE)
#> [1] TRUE

R Analysis

• Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns.
• Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
• Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
• Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

Python Solution

import pandas as pd
import networkx as nx

def intervals_overlap(start1, end1, start2, end2):
    return max(start1, start2) <= min(end1, end2)

path = "685 Overlapping Tasks.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=8)
test = pd.read_excel(path, usecols="E", nrows=3)

input['interval_start'] = pd.to_datetime(input['Planned Start Date'])
input['interval_end'] = pd.to_datetime(input['Planned End Date'])

tasks = pd.merge(input, input, how='cross')
tasks = tasks[tasks['Task_x'] < tasks['Task_y']]
tasks['overlap'] = tasks.apply(
    lambda row: intervals_overlap(
        row['interval_start_x'], row['interval_end_x'],
        row['interval_start_y'], row['interval_end_y']
    ),
    axis=1
)
tasks = tasks[tasks['overlap']][['Task_x', 'Task_y']]

g = nx.Graph()
g.add_edges_from(tasks.values)
subgraphs = [", ".join(sorted(component)) for component in nx.connected_components(g)]
subgraphs_df = pd.DataFrame({'ans': sorted(subgraphs)})

print(subgraphs_df['ans'].equals(test['Anwer Expected']))  # True

Python

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.

Related Challenges

• Excel Challenge 686
• Excel Challenge 687
• Excel Challenge 688